import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.sql.*;
import java.util.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Vector;

public class DatabaseCtrl {
	static String driver = "org.hsqldb.jdbcDriver";
	static String url = "jdbc:hsqldb:hsql://localhost";
	static String user = "sa";
	static String pass = "";
	static private Connection con;
	static private Connection con2;
	
	public static void init() throws Exception{
		//if(con != null)return;
		Class.forName(driver);
		con = DriverManager.getConnection(url, user, pass);
	}
	public static void initCon2() throws Exception{
		//if(con2 != null)return;
		Class.forName(driver);
		con2 = DriverManager.getConnection(url, user, pass);
	}

	public static void close() throws Exception{
		if(con != null) con.close();
	}
	public static void close2() throws Exception{
		if(con2 != null) con2.close();
	}
	//����userData�� ������id, pw����
	public static void initTableUserData() throws SQLException{
		String sql = "create table userData(id varchar(120), pw varchar(120))";
		Statement stmt = con.createStatement();
		stmt.executeUpdate(sql);
		stmt.close();
	}
	//����remindData�� ������noteTitle, content, reminderTime, ways, mail, id����
	public static void initTableRemindData() throws SQLException{
		String sql = "create table remindData(noteTitle varchar(120), content varchar(500), reminderTime datetime, ";
		sql += "ways bigint, mail varchar(120), id varchar(120))";
		Statement stmt = con.createStatement();
		stmt.executeUpdate(sql);
		stmt.close();
	}
	
	//�����¼��userData
	public static void insertUserData(String id, String pw) throws Exception{
		String sql = "insert into userData (id, pw) values(?, ?)";
		try(PreparedStatement pstmt = con.prepareStatement(sql);){
			pstmt.setString(1, id);
			pstmt.setString(2, pw);
			pstmt.executeUpdate();
		}
	}
	
	//�����¼��remindData
	public static void insertRemindData(String noteTitle, String content, String reminderTime, int ways, String mail, String id) throws Exception{
		String sql = "insert into remindData (noteTitle, content, reminderTime, ways, mail, id) values(?, ?, ?, ?, ?, ?)";
		try(PreparedStatement pstmt = con.prepareStatement(sql);){
			pstmt.setString(1, noteTitle);
			pstmt.setString(2, content);
			pstmt.setString(3, reminderTime);
			pstmt.setInt(4, ways);
			pstmt.setString(5, mail);
			pstmt.setString(6, id);
			pstmt.executeUpdate();
		}
	}
	
	//ɾ����¼
	public static void del(String noteTitle) throws Exception{
		System.out.println("ɾ��ģ�鿪ʼ��noteTitle = " + noteTitle);
		noteTitle = noteTitle.replaceAll("\\s*","").replaceAll("[^(\\u4e00-\\u9fa5)]","");
		noteTitle = "\'" + noteTitle + "\'";

		System.out.println("notetitle=" + noteTitle);

		String sql = "delete from remindData where noteTitle =" + noteTitle;
		try(Statement stmt = con.createStatement();){
			stmt.executeUpdate(sql);
		}
		
	}
	
	//��֤��½
	public static boolean validateLogin(String id, String pw) throws Exception {
		boolean isSuc;
		String sql = "select * from userData where id = ? and pw = ?";
		try(PreparedStatement pstmt = con.prepareStatement(sql);){
			pstmt.setString(1, id);
			pstmt.setString(2, pw);
			ResultSet rs = pstmt.executeQuery();
			isSuc = rs.next();
			rs.close();
			//pstmt.close();
		}
		return isSuc;
	}
	
	//�������ѱ���
	public static  Vector getRemindTitle() throws SQLException{
		Vector v = new Vector();
		int i = 0;
		String sql = "select noteTitle from remindData where id = " + "\'" + LoginInterface.onlineID + "\'";
		System.out.println("���ر����б�" + sql);
		Statement stmt = con2.createStatement();
		try(ResultSet rs = stmt.executeQuery(sql);){
			while(rs.next()) {
				v.addElement(rs.getString("noteTitle"));
				System.out.println(v.get(i++));
			}
		}
		stmt.close();
		return v;
	}
	//������������
	public static  Vector getRemindContent() throws SQLException{
		Vector v = new Vector();
		int i = 0;
		String sql = "select content from remindData where id = " + "\'" + LoginInterface.onlineID + "\'";
		System.out.println("���������б�" + sql);
		Statement stmt = con2.createStatement();
		try(ResultSet rs = stmt.executeQuery(sql);){
			while(rs.next()) {
				v.addElement(rs.getString("content"));
				System.out.println(v.get(i++));
			}
		}
		stmt.close();
		return v;
	}
	
	//��������ʱ��
	public static  Vector getRemindTime() throws SQLException{
		
		Timestamp t = new Timestamp(System.currentTimeMillis());
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String dateString =  dateFormat.format(t);
		
		Vector v = new Vector();
		int i = 0;
		String sql = "select reminderTime from remindData where id = " + "\'" + LoginInterface.onlineID + "\'";
		System.out.println("����ʱ���б�" + sql);
		Statement stmt = con2.createStatement();
		try(ResultSet rs = stmt.executeQuery(sql);){
			while(rs.next()) {
				
				t = rs.getTimestamp("reminderTime");
				dateString =  dateFormat.format(t);
				v.addElement(dateString);
				System.out.println(v.get(i++));
			}
		}
		stmt.close();
		return v;
	}
	
	//�������ѷ�ʽ
	public static  Vector getRemindWay() throws SQLException{
		Vector v = new Vector();
		int i = 0;
		String sql = "select ways from remindData where id = " + "\'" + LoginInterface.onlineID + "\'";
		System.out.println("���ط�ʽ�б�" + sql);
		Statement stmt = con2.createStatement();
		try(ResultSet rs = stmt.executeQuery(sql);){
			while(rs.next()) {
				v.addElement(rs.getInt("ways")+"");
				System.out.println(v.get(i++));
			}
		}
		stmt.close();
		return v;
	}
	
	
	//�����ʼ��б�
	public static  Vector getRemindMail() throws SQLException{
		Vector v = new Vector();
		int i = 0;
		String sql = "select mail from remindData where id = " + "\'" + LoginInterface.onlineID + "\'";
		System.out.println("�����ʼ��б�" + sql);
		Statement stmt = con2.createStatement();
		try(ResultSet rs = stmt.executeQuery(sql);){
			while(rs.next()) {
				v.addElement(rs.getString("mail"));
				System.out.println(v.get(i++));
			}
		}
		stmt.close();
		return v;
	}
	//����ʱ�䷵����������noteTitle, noteContent, mail, id;
	//������noteTitle, content, reminderTime, ways, mail, id����
	public static  String[] getDetail(Timestamp t) throws Exception{
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String dateString =  dateFormat.format(t);
		dateString = "\'" + dateString + "\'";
		String[] detail;
		detail = new String[4];
		String sql = "select * from remindData where id = " + "\'" + LoginInterface.onlineID + "\'";
		sql += " and reminderTime = " + dateString;
		
		System.out.println(sql);

		Statement stmt = con.createStatement();
		try(ResultSet rs = stmt.executeQuery(sql);){
			
			System.out.println("���ڼ�¼������");
			while(rs.next()) {
				detail[0] = rs.getString("noteTitle");
				detail[1] = rs.getString("content");
				detail[2] = rs.getString("mail");
				detail[3] = rs.getString("id");
				System.out.printf("����ʱ��%s%s%s%s\n", detail[0], detail[1], detail[2], detail[3]);
			}
			
		}
		stmt.close();
		return detail;
	}
	
	
	//������������noteTitle, noteContent, mail, id;
	//������noteTitle, content, reminderTime, ways, mail, id����
	public static  String[] getDetail(String noteTitle) throws Exception{
		noteTitle = noteTitle.replaceAll("\\s*","").replaceAll("[^(\\u4e00-\\u9fa5)]","");
		String[] detail;
		detail = new String[4];
		noteTitle = "\'" + noteTitle + "\'";
		String sql = "select * from remindData where id = " + "\'" + LoginInterface.onlineID + "\'";
		sql += " and noteTitle = " + noteTitle;
		
		System.out.println(sql);

		Statement stmt = con.createStatement();
		try(ResultSet rs = stmt.executeQuery(sql);){
			
			System.out.println("���ڼ�¼������");
			while(rs.next()) {
				detail[0] = rs.getString("noteTitle");
				detail[1] = rs.getString("content");
				detail[2] = rs.getString("mail");
				detail[3] = rs.getString("id");
				System.out.printf("%s%s%s%s", detail[0], detail[1], detail[2], detail[3]);
				System.out.println("���0");
			}
			System.out.println("���1");
			System.out.printf("detail[]=%s,%s,%s,%s", detail[0], detail[1], detail[2], detail[3]);
			
		}
		stmt.close();
		System.out.println("���2");
		return detail;
	}
	
	//������������way
	//������noteTitle, content, reminderTime, ways, mail, id����
	public static  int getDetailWay(String noteTitle) throws Exception{
		int way = 0;
		noteTitle = "\'" + noteTitle + "\'";
		String sql = "select * from remindData where id = " + "\'" + LoginInterface.onlineID + "\'";
		sql += " and noteTitle = " + noteTitle;
		
		System.out.println("���4");
		//�ٴν�������
		//initCon2(); //�ٴθ�ֵconΪconnection
		Statement stmt = con.createStatement();
		try(ResultSet rs = stmt.executeQuery(sql);){
			
			while(rs.next()) {
				way = rs.getInt("ways");				
			}

		}
		stmt.close();//�ر�statement
		return way;
	}
	//���������û�����Ϊ�ʼ���timestamp Vector  
	public static  Vector getDetailTimestampMail() throws Exception{
		Timestamp t = new Timestamp(System.currentTimeMillis());
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String dateString =  dateFormat.format(t);
		Vector v = new Vector();
		String sql = "select * from remindData where";
		sql += " ways = 2" + " order by reminderTime desc";
		//sql += " and reminderTime >= " + dateString  + " and ways = 1" + " order by reminderTime desc "; 
		System.out.println(sql);
		Statement stmt = con.createStatement();
		try(ResultSet rs = stmt.executeQuery(sql);){
			while(rs.next()) {
				t = rs.getTimestamp("reminderTime");
				v.addElement(t);
			}
		}
		stmt.close();
		return v;
	}
	
	
	//���������û����ѷ�ʽΪ������timestamp 
	public static  Vector getDetailTimestamp() throws Exception{
		Timestamp t = new Timestamp(System.currentTimeMillis());
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String dateString =  dateFormat.format(t);
		Vector v = new Vector();
		String sql = "select * from remindData where";
		sql += " ways = 1" + " order by reminderTime desc";
		//sql += " and reminderTime >= " + dateString  + " and ways = 1" + " order by reminderTime desc "; 
		System.out.println(sql);
		Statement stmt = con.createStatement();
		try(ResultSet rs = stmt.executeQuery(sql);){
			while(rs.next()) {
				t = rs.getTimestamp("reminderTime");
				v.addElement(t);
			}
		}
		stmt.close();
		return v;
	}
	//������������timestamp(�޸�noteʱ��)
	//������noteTitle, content, reminderTime, ways, mail, id����
	public static  Timestamp getDetailTimestamp(String noteTitle) throws Exception{
		Timestamp t = new Timestamp(1983989987);
		noteTitle = "\'" + noteTitle + "\'";
		String sql = "select * from remindData where id = " + "\'" + LoginInterface.onlineID + "\'";
		sql += " and noteTitle = " + noteTitle;
		Statement stmt = con.createStatement();
		try(ResultSet rs = stmt.executeQuery(sql);){
			while(rs.next()) {
				t = rs.getTimestamp("reminderTime");
			}

		}
		stmt.close();
		return t;
	}
	//main������
	public static void main(String[] args) throws Exception{
		init();
		
		try {
			initTableRemindData();
		}finally {
			close();
		}
		

	}

}
